package com.anolesoft.twelvePlan.mapper;

import com.anolesoft.twelvePlan.entity.MesGyInfoDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author zyq
 * @date 2022/5/27
 * 工艺计划表定时任务
 */
@Mapper
public interface MesGyInfoMatchMapper {
    // 机型毛料库存，毛料库存总数
    @Select("select a.objuid, a.jx, a.partid, a.factory_id, b.jxmlstock, b.mlstocktotal\n" +
            "  from mes_gy_info a\n" +
            " inner join (select b.jixingbianhao,\n" +
            "                    b.lingjiantuhao,\n" +
            "                    b.jiaofuchejian,\n" +
            "                    b.jieshouchejian,\n" +
            "                    b.jiaofushunxu,\n" +
            "                    b.jxmlstock,\n" +
            "                    b.mlstocktotal\n" +
            "               from (select b.*,\n" +
            "                            row_number() over(partition by b.jixingbianhao, b.lingjiantuhao, b.jiaofuchejian, b.jieshouchejian, b.jiaofushunxu order by b.jxmlstock) as aa\n" +
            "                       from (select b.*,\n" +
            "                                    sum(b.kucunshuliang) over(partition by b.lingjiantuhao, b.jiaofuchejian, b.jieshouchejian, b.jiaofushunxu) as mlstocktotal\n" +
            "                               from (select b.*,\n" +
            "                                            sum(b.kucunshuliang) over(partition by b.jixingbianhao, b.lingjiantuhao, b.jiaofuchejian, b.jieshouchejian, b.jiaofushunxu) as jxmlstock\n" +
            "                                       from sfscxt.mv_skmes_lingjianku_mx@lean b\n" +
            "                                      inner join (select distinct a.factory_id\n" +
            "                                                   from mes_gy_info a) a\n" +
            "                                         on b.jieshouchejian = a.factory_id\n" +
            "                                      where b.lingliaoleibie = '毛料') b) b) b\n" +
            "              where b.aa = 1) b\n" +
            "    on a.jx = b.jixingbianhao\n" +
            "   and b.lingjiantuhao like concat(a.partid, '%')\n" +
            "   and a.llworkshop = b.jiaofuchejian\n" +
            "   and a.factory_id = b.jieshouchejian\n" +
            "   and a.lldeliveryorder = b.jiaofushunxu\n")
    List<MesGyInfoDO> queryMaoLiao();

    // 机型半成品库存，半成品库存总数
    @Select("select a.objuid, a.jx, a.partid, a.factory_id, b.bcpstocknum, b.bcpstocktotal\n" +
            "  from mes_gy_info a\n" +
            " inner join (select b.jixingbianhao,\n" +
            "                    b.lingjiantuhao,\n" +
            "                    b.jiaofuchejian,\n" +
            "                    b.jieshouchejian,\n" +
            "                    b.jiaofushunxu,\n" +
            "                    b.bcpstocknum,\n" +
            "                    b.bcpstocktotal\n" +
            "               from (select b.*,\n" +
            "                            row_number() over(partition by b.jixingbianhao, b.lingjiantuhao, b.jiaofuchejian, b.jieshouchejian, b.jiaofushunxu order by b.bcpstocknum) as aa\n" +
            "                       from (select b.*,\n" +
            "                                    sum(b.kucunshuliang) over(partition by b.lingjiantuhao, b.jiaofuchejian, b.jieshouchejian, b.jiaofushunxu) as bcpstocktotal\n" +
            "                               from (select b.*,\n" +
            "                                            sum(b.kucunshuliang) over(partition by b.jixingbianhao, b.lingjiantuhao, b.jiaofuchejian, b.jieshouchejian, b.jiaofushunxu) as bcpstocknum\n" +
            "                                       from sfscxt.mv_skmes_lingjianku_mx@lean b\n" +
            "                                      inner join (select distinct a.factory_id\n" +
            "                                                   from mes_gy_info a) a\n" +
            "                                         on b.jieshouchejian = a.factory_id\n" +
            "                                      where b.lingliaoleibie = '半成品') b) b) b\n" +
            "              where b.aa = 1) b\n" +
            "    on a.jx = b.jixingbianhao\n" +
            "   and a.partid = b.lingjiantuhao\n" +
            "   and a.llworkshop = b.jiaofuchejian\n" +
            "   and a.factory_id = b.jieshouchejian\n" +
            "   and a.lldeliveryorder = b.jiaofushunxu")
    List<MesGyInfoDO> queryBcp();

    
}
